<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class jasa extends CI_Controller {

    public function __construct() {
        parent::__construct();

        $this->load->library('auth');
        $this->load->library('base');
        $this->load->library('ajaxresponse');
        $this->load->library("phpexcel");
        $this->load->library("PHPExcel/iofactory");

        $this->load->model("report/m_jasa");
        $this->load->model("crud/m_info_usaha");
    }

    function index(){
        $this->jasa1();
    }

    function jasa1(){
        $this->auth->is_logged_in('form/login');
        $this->base->head_html('Laporan Jasa');
        $data["info_usaha"] = $this->m_info_usaha->get()->result();
        $this->load->view('pengurus/br-08/jasa/jasa', $data);
    }
    
    function jasa2(){
        $this->auth->is_logged_in('form/login');
        $this->base->head_html('Laporan Jasa');
        $data["info_usaha"] = $this->m_info_usaha->get()->result();
        $this->load->view('pengurus/br-08/jasa/jasa', $data);
    }

    function rekapjasa(){
        $this->auth->is_logged_in('form/login');
        $this->base->head_html('Laporan Jasa');
        $data["info_usaha"] = $this->m_info_usaha->get()->result();
        $this->load->view('pengurus/br-08/jasa/rekapjasa', $data);
    }

    function cetakjasa(){
        $detail = $this->input->post("CETAKDETAIL");
        if ($detail=="Y"){
            $this->calc_jasa2();
        }else $this->calc_jasa1();
    }

    function calc_jasa2(){
        $date = $this->input->post("TGL_JASA");

        $result = $this->m_jasa->sum_jasa2($date);

        // --------------------------- create excel ---------------------------------//
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setTitle("Laporan Jasa")
                ->setDescription("description");

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(false);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setAutoSize(true);

        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(1);

        $objPHPExcel->getActiveSheet()->getStyle('A8')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
        $objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A8')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('A8'), 'B8:H8' );

        $objPHPExcel->getActiveSheet()->setCellValue('A1', "KOPERASI KARYAWAN TOTAL INDONESIE");
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "JL.JEND.SUDIRMAN H2 NO.5-6 BALIKPAPAN PERMAI");
        $objPHPExcel->getActiveSheet()->setCellValue('A3', "BALIKPAPAN");
        $objPHPExcel->getActiveSheet()->setCellValue('A4', "Tlp.: 0542-427723, Fac.: 0542-427722");
        $sheet = $objPHPExcel->getActiveSheet();
        $sheet->mergeCells('A1:H1');
        $sheet->mergeCells('A2:H2');
        $sheet->mergeCells('A3:H3');
        $sheet->mergeCells('A4:H4');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A6', "JASA BULAN : Oktober 2010");
        $objPHPExcel->getActiveSheet()->setCellValue('H6', "TANGGAL : 10-02-2012");

        $objPHPExcel->getActiveSheet()->setCellValue('A8', "No");
        $objPHPExcel->getActiveSheet()->setCellValue('B8', "No. Anggota");
        $objPHPExcel->getActiveSheet()->setCellValue('C8', "Nama Anggota");
        $objPHPExcel->getActiveSheet()->setCellValue('D8', "Jenis Kredit");
        $objPHPExcel->getActiveSheet()->setCellValue('E8', "SD Bulan Lalu");
        $objPHPExcel->getActiveSheet()->setCellValue('F8', "Bulan Ini");
        $objPHPExcel->getActiveSheet()->setCellValue('G8', "SD Bulan Ini");
        $objPHPExcel->getActiveSheet()->setCellValue('H8', "Sub Total");

        $i = 8;
        $notrx = 0;
        foreach($result->result() as $row) {
                $i++;
                $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i-8);
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $row->ID_ANGGOTA);
                $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $row->NAMA_ANGGOTA);
                $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $row->URAIAN_USAHA);
                $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, ($row->JUMLAH_PINJAMAN-$row->PIUTANG));
                $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $row->MARGIN);
                $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, ($row->JUMLAH_PINJAMAN-$row->PIUTANG+$row->CICILAN_POKOK));
                $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, '0');
                
        }
        //$objPHPExcel->getActiveSheet()->setCellValue("E$i", "=SUM(E2:E".$i.")");
        //$objPHPExcel->getActiveSheet()->setCellValue("F$i", "=SUM(F2:F".$i.")");
        //$objPHPExcel->getActiveSheet()->setCellValue("G$i", "=SUM(G2:G".$i.")");
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HLaporan Jasa(2)\n!');
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

        // Save it as an excel 2003 file
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Laporan Jasa(2).xls"');
        header('Cache-Control: max-age=0');

        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }

    function calc_jasa1(){
        $date = $this->input->post("TGL_JASA");

        $result = $this->m_jasa->sum_jasa($date);
        

        // --------------------------- create excel ---------------------------------//
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setTitle("Laporan Jasa")
                ->setDescription("description");

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(false);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);

        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(1);

        $objPHPExcel->getActiveSheet()->getStyle('A8')->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
        $objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A8')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('A8'), 'B8:F8' );

        $objPHPExcel->getActiveSheet()->setCellValue('A1', "KOPERASI KARYAWAN TOTAL INDONESIE");
        $objPHPExcel->getActiveSheet()->setCellValue('A2', "JL.JEND.SUDIRMAN H2 NO.5-6 BALIKPAPAN PERMAI");
        $objPHPExcel->getActiveSheet()->setCellValue('A3', "BALIKPAPAN");
        $objPHPExcel->getActiveSheet()->setCellValue('A4', "Tlp.: 0542-427723, Fac.: 0542-427722");
        $sheet = $objPHPExcel->getActiveSheet();
        $sheet->mergeCells('A1:F1');
        $sheet->mergeCells('A2:F2');
        $sheet->mergeCells('A3:F3');
        $sheet->mergeCells('A4:F4');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $objPHPExcel->getActiveSheet()->setCellValue('A6', "JASA BULAN : Oktober 2010");
        $objPHPExcel->getActiveSheet()->setCellValue('F6', "TANGGAL : 10-02-2012");

        $objPHPExcel->getActiveSheet()->setCellValue('A8', "No");
        $objPHPExcel->getActiveSheet()->setCellValue('B8', "No. Anggota");
        $objPHPExcel->getActiveSheet()->setCellValue('C8', "Nama Anggota");
        $objPHPExcel->getActiveSheet()->setCellValue('D8', "Jenis Kredit");
        $objPHPExcel->getActiveSheet()->setCellValue('E8', "Jumlah");
        $objPHPExcel->getActiveSheet()->setCellValue('F8', "Sub Total");

        $i = 8;
        $notrx = 0;
        foreach($result->result() as $row) {
                $i++;
                $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i-8);
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $row->ID_ANGGOTA);
                $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $row->NAMA_ANGGOTA);
                $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $row->URAIAN_USAHA);
                $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $row->POTONGAN);
                $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $row->POTONGAN);

        }
        //$objPHPExcel->getActiveSheet()->setCellValue("E$i", "=SUM(E2:E".$i.")");
        //$objPHPExcel->getActiveSheet()->setCellValue("F$i", "=SUM(F2:F".$i.")");
        //$objPHPExcel->getActiveSheet()->setCellValue("G$i", "=SUM(G2:G".$i.")");
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HLaporan Jasa(1)\n!');
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

        // Save it as an excel 2003 file
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Laporan Jasa(1).xls"');
        header('Cache-Control: max-age=0');

        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }

    function calc_rekapjasa(){

        $date = $this->input->post("TGL_REKAP");

        // --------------------------- create excel ---------------------------------//
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setTitle("Rekap Jasa")
                ->setDescription("description");

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(false);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);

        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(1);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.75);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(1);


        for ( $m=0; $m<12; $m++){
            
            $result = $this->m_jasa->sum_rekap($date);

            $objPHPExcel->getActiveSheet()->getStyle('A'.$m+8)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
            $objPHPExcel->getActiveSheet()->getStyle('A'.$m+8)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A'.$m+8)->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('A'.$m+8), 'B'.($m+8).':C'.($m+8));

            $objPHPExcel->getActiveSheet()->setCellValue('A1', "KOPERASI KARYAWAN TOTAL INDONESIE");
            $objPHPExcel->getActiveSheet()->setCellValue('A2', "JL.JEND.SUDIRMAN H2 NO.5-6 BALIKPAPAN PERMAI");
            $objPHPExcel->getActiveSheet()->setCellValue('A3', "BALIKPAPAN");
            $objPHPExcel->getActiveSheet()->setCellValue('A4', "Tlp.: 0542-427723, Fac.: 0542-427722");
            $sheet = $objPHPExcel->getActiveSheet();
            $sheet->mergeCells('A1:C1');
            $sheet->mergeCells('A2:C2');
            $sheet->mergeCells('A3:C3');
            $sheet->mergeCells('A4:C4');
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

            $objPHPExcel->getActiveSheet()->setCellValue('A6', "REKAP JASA BULAN : Oktober 2010");
            $objPHPExcel->getActiveSheet()->setCellValue('C6', "TANGGAL : 10-02-2012");

            $objPHPExcel->getActiveSheet()->setCellValue('A8', "No");
            $objPHPExcel->getActiveSheet()->setCellValue('B8', "Jenis Usaha");
            $objPHPExcel->getActiveSheet()->setCellValue('C8', "Jumlah");

            $i = 8;
            $notrx = 0;
            foreach($result->result() as $row) {
                    $i++;
                    $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $i-8);
                    $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $row->URAIAN_USAHA);
                    $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $row->JUMLAH);

            }

        }
        //$objPHPExcel->getActiveSheet()->setCellValue("E$i", "=SUM(E2:E".$i.")");
        //$objPHPExcel->getActiveSheet()->setCellValue("F$i", "=SUM(F2:F".$i.")");
        //$objPHPExcel->getActiveSheet()->setCellValue("G$i", "=SUM(G2:G".$i.")");
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HLaporan Rekap Jasa\n!');
        $objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&B' . $objPHPExcel->getProperties()->getTitle() . '&RPage &P of &N');

        // Save it as an excel 2003 file
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Laporan Rekap Jasa.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
    }

    
}

?>
